﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_CFG_ATTACH_FILE_Get_Attach_File_List')
	BEGIN
		DROP Procedure usp_UPDMS_CFG_ATTACH_FILE_Get_Attach_File_List
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/****************************************************************************** 
**	Name : usp_UPDMS_CFG_ATTACH_FILE_Get_Attach_File_List   
**	Desc : 첨부파일 목록을 조회한다. 
**	Test Exec Query : Exec usp_UPDMS_CFG_ATTACH_FILE_Get_Attach_File_List 'ko'
**	Called by : Cfg_Dac_UPDMS_CFG_ATTACH_FILE.cs
**	Program ID : Cfg1014m
**	Auth : 송시명
**	Date : 2010-10-15
******************************************************************************* 
**	Change History 
******************************************************************************* 
**	Date:		Author:		Description: 
**	--------	--------	--------------------------------------- 
**	
*******************************************************************************/ 
CREATE PROC [dbo].[usp_UPDMS_CFG_ATTACH_FILE_Get_Attach_File_List]
@ls_lang_set nvarchar(2)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

SELECT x.Seq,
       x.Table_Nm,
       x.Column_Nm,
       ucp.Pgm_Id,
       CASE @ls_lang_set WHEN 'ko' THEN ucp.Pgm_Nm ELSE ucp.Pgm_Nm_Eng END Pgm_Nm,
       x.Attach_File_Nm,
       'Y' AS Exist_Db
  FROM UPDMS_COM_PGM AS ucp WITH(NOLOCK)
  JOIN (
       SELECT 'Car1010m' AS Pgm_Id,
              Seq,
              'UPDMS_CAR_INFO' AS Table_Nm,
              'Attach_File1' AS Column_Nm,
              Attach_File1 AS Attach_File_Nm
         FROM UPDMS_CAR_INFO WITH(NOLOCK)
        UNION ALL
        SELECT 'Crd1010m',
               Seq,
               'UPDMS_CRD_INFO',
               'Attach_File1',
               Attach_File1
          FROM UPDMS_CRD_INFO WITH(NOLOCK)
       ) x
    ON ucp.Pgm_Id = x.Pgm_Id
 WHERE LEN(x.Attach_File_Nm) > 5


/* DB파일 - 멀티 */
SELECT x.Activity_Id,
       ucp.Pgm_Id,
       CASE @ls_lang_set WHEN 'ko' THEN ucp.Pgm_Nm ELSE ucp.Pgm_Nm_Eng END Pgm_Nm,
	   x.File_Id,
       x.File_Dir,
	   x.Attach_File_Nm,
       'Y' AS Exist_Db
  FROM UPDMS_COM_PGM AS ucp WITH(NOLOCK)
  JOIN (
       SELECT Activity_Id,
              Activity_Cd,
              File_Id,
              File_Dir,
              File_Nm AS Attach_File_Nm
         FROM UPDMS_COM_FILE WITH(NOLOCK)
       ) x
    ON ucp.Pgm_Id = x.Activity_Cd
 WHERE LEN(x.Attach_File_Nm) > 1

GO